package com.spar.openprojects.server;

import java.io.IOException;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.spar.openprojects.server.datasource.DatasourceInformixETime;
import com.spar.openprojects.server.datasource.DatasourceMySqlOpenprojects;
import com.spar.openprojects.shared.ETimeItem;
import com.spar.openprojects.shared.rowmapper.OrderExcelRowMapper;
import com.spar.openprojects.shared.utilities.LogManager;
import com.sql.database.Database;
import com.sql.database.IConnectionFactory;
import com.sql.database.IRowMapper;

public class ETimeExcelExportServlet extends HttpServlet {
	/**
	 * 
	 */
	private static final long serialVersionUID = -3768817950809784492L;

	@SuppressWarnings({ "unchecked", "rawtypes" })
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		IConnectionFactory connectionFactory = new DatasourceInformixETime();
		IConnectionFactory logConnectionFactory = new DatasourceMySqlOpenprojects();
		LogManager logManager = new LogManager(logConnectionFactory);
		try {
			String projectId = request.getParameter("projectId");

			Database database = new Database(connectionFactory);
			IRowMapper rowMapper = new OrderExcelRowMapper();
			String sqlStatement = String
					.format("select c.cat_id || ' - ' || c.description projectname,  t.nname || '(' || t.created_by || ')' membername , "
							+ "year(t.date)  * 100 + month(t.date) date, "
							+ "round(sum(t.duration)/8,2) time from time t inner join subcategory s "
							+ "on t.sc_id = s.id inner join category c on c.cat_id = s.cat_id "
							+ "where c.cat_id in ({0}) and s.cat_id in ({0}) and "
							+ "(t.abt_kbez like 'ICS%' or t.abt_kbez like 'HZ4%') and t.valid = 1 "
							+ "group by c.description, c.cat_id, t.nname, t.created_by, 3 "
							+ "order by 1, 3, 2", projectId);

			List<ETimeItem> eTimeItems = new ArrayList<ETimeItem>();
			try {
				eTimeItems = database.ExecuteQuery(sqlStatement, rowMapper);
			} catch (Exception e) {
				logManager.Log("Error", "load etime", e.getMessage());
			}

			Format dateFormat = new SimpleDateFormat("yyyy_MM_dd");
			String orderDate = "";
			// if (orderExcel.getOrder().getDate() != null) {
			// orderDate = dateFormat.format(orderExcel.getOrder().getDate());
			// }
			// String filename = "etime_Auswertung_"
			// + orderExcel.getProject().getNumber() + "_"
			// + orderExcel.getProject().getDescription() + "_"
			// + orderDate + ".xls";
			// response.setContentType("application/octet-stream");
			// response.setHeader("Content-Disposition",
			// "attachment; filename=\""
			// + filename + "\"");
			//
			// String path = "./webapps/openprojects/";
			// String pathDebug = "./";
			// InputStream inputStream = new BufferedInputStream(
			// new FileInputStream(path + "sampleetimeform.xls"));
			// Map beans = new HashMap();
			// beans.put("order", orderExcel);
			// XLSTransformer transformer = new XLSTransformer();
			// Workbook wb = transformer.transformXLS(inputStream, beans);
			// ServletOutputStream op = response.getOutputStream();
			// wb.write(op);
			// op.flush();
			// op.close();
			//
			// logManager.Log("Info", "export etime excel", filename
			// + " successfully exported");
		} catch (Exception ex) {
			logManager.Log("Error", "export etime excel", ex.getMessage());
		}
	}
}
